package de.lianowar.whereismymoney.sql;

import java.util.ArrayList;
import java.util.List;

import de.lianowar.whereismymoney.sql.data.Account;
import de.lianowar.whereismymoney.sql.data.MoneyAmount;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseHandler extends SQLiteOpenHelper {

    private static final int DATABASE_VERSION = 1;    
    // Database Name
    private static final String DATABASE_NAME = "WhereIsMyMoney";
 
    // MoneyAmounts table name
    private static final String TABLE_MONEY_AMOUNT = "moneyAmount";
	
    private static final String KEY_ID = "id";
    private static final String KEY_AMOUNT = "amount";
    private static final String KEY_COMMENT= "comment";    
    private static final String KEY_DATE = "date";
    private static final String KEY_ACCOUNT = "accountFK";
    
    private static final String KEY_NAME = "name";
    
    public DatabaseHandler(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
 
    
	@Override
	public void onCreate(SQLiteDatabase db)
	{
		// TODO Auto-generated method stub
		createAccount(db,"Accounts");		
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
	     // Drop older table if existed
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_MONEY_AMOUNT);
 
        // Create tables again
        onCreate(db);
	}
	
	
	public void createAccountDetails(SQLiteDatabase db,String pTableName)
	{
		 String CREATE_MoneyAmountS_TABLE = "CREATE TABLE IF NOT EXISTS " + TABLE_MONEY_AMOUNT + "("
	                + KEY_ID + " INTEGER PRIMARY KEY," + KEY_AMOUNT + " FLOAT,"
	                + KEY_COMMENT + " TEXT," +  KEY_DATE + " TEXT ," +  KEY_ACCOUNT + " INT )";
	        db.execSQL(CREATE_MoneyAmountS_TABLE);
	        Log.e("SQL-Command", CREATE_MoneyAmountS_TABLE);
	}
	public void createAccount(SQLiteDatabase db,String pTableName)
	{
		 String CREATE_MoneyAmountS_TABLE = "CREATE TABLE IF NOT EXISTS " + pTableName + "("
	                + KEY_ID + " INTEGER PRIMARY KEY,  name TEXT )";
	        db.execSQL(CREATE_MoneyAmountS_TABLE);
	        Log.e("SQL-Command", CREATE_MoneyAmountS_TABLE);
	}
	public void dropDatabase(String pTableName) {
		  SQLiteDatabase db = this.getWritableDatabase();
		  
		    
		  db.execSQL("DROP TABLE " + pTableName);

	}
	
	public void insertAccount(String pAccount,String pTable)
	{
		 SQLiteDatabase db = this.getWritableDatabase();
		  
		    ContentValues values = new ContentValues();
		    values.put(KEY_NAME,pAccount); // Contact Name
		    // Inserting Row
		    db.insert(pTable, null, values);
	}
	
	public String getAccountById(int pId)
	{
		String countQuery = "";
		try
		{
			countQuery = "SELECT NAME FROM ACCOUNTS WHERE id = "+ pId;
	        SQLiteDatabase db = this.getReadableDatabase();
	        
	        Cursor cursor = db.rawQuery(countQuery, null);
	    	        // return count
	        cursor.moveToFirst();
	        return cursor.getString(0);
		}
		catch(Exception ex)
		{
		Log.e("EXCEPTION", countQuery);
		return "Name:";
		}
	}
	
	// Adding new MoneyAmount
	public void addMoneyAmount(MoneyAmount pMoneyAmount) {
		  SQLiteDatabase db = this.getWritableDatabase();
		  
		    ContentValues values = new ContentValues();
		    values.put(KEY_AMOUNT, pMoneyAmount.getAmount()); // Contact Name
		    values.put(KEY_COMMENT, pMoneyAmount.getComment()); // Contact Phone Number
		    values.put(KEY_DATE, ""+pMoneyAmount.getDate()); // Contact Phone Number
		    values.put(KEY_ACCOUNT, pMoneyAmount.getAccount_fk());
		    // Inserting Row
		    db.insert(TABLE_MONEY_AMOUNT, null, values);

	}
	 

	
	// Getting single MoneyAmount
	public MoneyAmount getMoneyAmount(int id) {
		return new MoneyAmount();
	}
	 
	//Getting All MoneyAmounts
	public ArrayList<MoneyAmount> getAllMoneyAmounts(int pFK) {
		        // Select All Query
			ArrayList<MoneyAmount> contactList = new ArrayList<MoneyAmount>();
		        String selectQuery = "SELECT  * FROM " + TABLE_MONEY_AMOUNT +" WHERE " + KEY_ACCOUNT +" LIKE "+pFK;
		 
		        SQLiteDatabase db = this.getWritableDatabase();
		        Cursor cursor = db.rawQuery(selectQuery, null);
		 
		        // looping through all rows and adding to list
		        if (cursor.moveToFirst()) {
		            do {
		            	MoneyAmount contact = new MoneyAmount();
		                contact.setId((Integer.parseInt(cursor.getString(0))));
		                contact.setAmount(cursor.getFloat(1));
		                contact.setComment(cursor.getString(2));
		                contact.setDate(cursor.getString(3));
		                // Adding contact to list
		                contactList.add(contact);
		            } while (cursor.moveToNext());
		        }
		 
		        // return contact list
		        Log.e("SELECT", "Anzahl: "+contactList.size());
		       
		        return contactList;
		}
	 
	public float sumMoneyAmount(int id)
		{
			SQLiteDatabase db = this.getWritableDatabase();
			String selectQuery = "SELECT  SUM("+KEY_AMOUNT+") AS 'SUMME' FROM " + TABLE_MONEY_AMOUNT +" WHERE " + KEY_ACCOUNT +" LIKE "+id;
			Log.e("SQL-String", selectQuery);
			Cursor cursor = db.rawQuery(selectQuery, null);
			 cursor.moveToFirst();	
			return cursor.getFloat(0);
		}
		
		//Getting All MoneyAmounts
	public ArrayList<Account> getAllAccounts() {
			ArrayList<Account> contactList = new ArrayList<Account>();
		        // Select All Query
		        String selectQuery = "SELECT  * FROM " + "accounts";
		 
		        SQLiteDatabase db = this.getWritableDatabase();
		        Cursor cursor = db.rawQuery(selectQuery, null);
		 
		        // looping through all rows and adding to list
		        if (cursor.moveToFirst()) {
		            do {
		            	Account contact = new Account();
		                contact.setId((Integer.parseInt(cursor.getString(0))));
		                contact.setName(cursor.getString(1));
		               
		                // Adding contact to list
		                contactList.add(contact);
		            } while (cursor.moveToNext());
		        }
		 
		        // return contact list
		        Log.e("SELECT", "Gefunden: "+ contactList.size());
		        return contactList;
		}
		
	// Getting MoneyAmounts Count
	public int getMoneyAmountsCount() {
		 String countQuery = "SELECT  * FROM " + TABLE_MONEY_AMOUNT;
	        SQLiteDatabase db = this.getReadableDatabase();
	        Cursor cursor = db.rawQuery(countQuery, null);
	        
	        // return count
	        return cursor.getCount();
	}
	// Updating single MoneyAmount
	public int updateMoneyAmount(MoneyAmount MoneyAmount) {
		return 0;
	}
	 
	// Deleting single MoneyAmount
	public void deleteMoneyAmount(MoneyAmount MoneyAmount) {}


}
